package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

@SuppressWarnings({ "unchecked", "rawtypes" })
public class JdbcUtil {

	static String driverName = "oracle.jdbc.driver.OracleDriver";
	static String url = "jdbc:oracle:thin:@172.16.7.200:1521:ZHFWDB";
	static String user = "work4";
	static String password = "123456";

	public static int executeUpdate(String sql) {
		return executeUpdate(sql, null);
	}

	public static int executeUpdate(String sql, Object[] params) {
		int affectedLine = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getSysDbConnection();
			preparedStatement = connection.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}
			}

			affectedLine = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(preparedStatement);
			close(connection);
		}
		return affectedLine;
	}

	public static int excuteCount(String sql) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		int recordCount = 0;
		try {
			connection = getSysDbConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();

			if (resultSet.next()) {
				recordCount = resultSet.getInt(1);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(resultSet);
			close(preparedStatement);
			close(connection);
		}
		return recordCount;
	}

	public static List excuteQuery(String sql, Object[] params) {
		System.out.println("query sql:" + sql);
		System.out.println("query params:" + params);

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ResultSetMetaData rsmd = null;

		int columnCount = 0;
		try {
			connection = getSysDbConnection();
			preparedStatement = connection.prepareStatement(sql);

			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}
			}
			resultSet = preparedStatement.executeQuery();

			rsmd = resultSet.getMetaData();

			columnCount = rsmd.getColumnCount();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		List list = new ArrayList();
		try {
			while (resultSet.next()) {
				Map map = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					map.put(rsmd.getColumnLabel(i), resultSet.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(resultSet);
			close(preparedStatement);
			close(connection);
		}
		return list;
	}

	public static Map excuteQueryOne(String sql, Object[] params) {
		List list = excuteQuery(sql, params);
		if (list != null && list.size() > 0)
			return (Map) list.get(0);
		return new HashMap();
	}

	public static Connection getSysDbConnection() throws SQLException {
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		// 获取数据库连接
		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}

	public static void close(Connection conn) {
		try {
			if (conn != null && !conn.isClosed())
				conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public static void close(Statement stmt) {
		try {
			if (stmt != null)
				stmt.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public static void close(ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public static void filterNullValue(List resList) {
		for (Iterator it = resList.iterator(); it.hasNext();) {
			Map res = (Map) it.next();

			Object s[] = res.keySet().toArray();
			for (int i = 0; i < res.size(); i++) {
				if (res.get(s[i]) == null) {
					res.put(s[i], "&nbsp;");
				} else if (strOf(res.get(s[i])).trim().equals("")) {
					res.put(s[i], "&nbsp;");
				}
			}
		}
	}

	public static String strOf(Object o) {
		return o != null ? o.toString() : "";
	}

	public static int intOf(Object obj) {
		try {
			return Integer.valueOf(obj.toString()).intValue();
		} catch (Exception e) {
		}
		return 0;
	}
}